package pers.cz.sql;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.statement.insert.Insert;

/**
 * SQL工具，主要用户生成回退语句
 * @program: PostGirl-panent
 * @description: SqlUtils
 * @author: Cheng Zhi
 * @create: 2022-03-28 16:58
 **/
public class SqlUtils {

    /**
     * 生成回滚语句
     * @param sql
     * @return
     */
    public static String getRollbackSql(String sql) {

        String rollBackSql = "";

        try {
            SqlType sqlType = SqlParserTool.getSqlType(sql);
            if (sqlType.equals(SqlType.INSERT)) {
                Insert statement = (Insert) SqlParserTool.getStatement(sql);
                rollBackSql = SqlParserTool.insertToDelete(statement);
            } else {
                rollBackSql = "-- 暂时只支持Insert语句";
            }
        } catch (Exception e) {
            rollBackSql = "-- 抱歉....生成语句异常了";
        }
         return rollBackSql;
    }

    /**
     * 清洗sql,将一句可以正常在数据库执行的sql转换为sql模板，即 select * from test_table where id = 24; => select * from test_table id = ?;
     * @param sql
     * @return
     */
    public static String purgeSql(String sql) {

        try {
            return SqlParserTool.formatSql(sql, "?");
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
        return sql;
    }

    /**
     * 获取表名
     * @param sql
     * @return
     */
    public static String getTableName(String sql) {
        return SqlParserTool.getSingleTableName(sql);
    }

    public static void main(String[] args) {

        // String sql = "insert into csf.csf_registerbycode_info(id,service_code,xml_content1,flag) values((select max(id) +1 from csf.csf_registerbycode_info),'7927','<?xml version=\"1.0\" encoding=\"utf-8\"?><method name=\"getUserGeneralInfoByMask\" interface=\"com.asiainfo.crm.srv.standard.fuse.service.interfaces.IFuseCommonCSV\" implClass=\"\" extendType=\"2\"  desc=\"\" signature=\"83e1a10bbf80f1ec\"><inParam><inMap type=\"java.util.Map\"/></inParam><return type=\"java.util.Map\"></return></method>','');";
        String sql = "insert into ZD.SYS_PARTITION_LOGIC_PARAM(BASE_NAME,PARAM_NAME,PARAM_VALUE,PARAM_COMMENT) values('CA_BILL','GET_HIS_DB','local cur_time=os.date(\"%Y%m%d%H\",os.time());\n" +
                "\n" +
                "local t1=tonumber(string.sub(cur_time,0,4))\n" +
                "\n" +
                "local t2=tonumber(string.sub(cur_time,5,6))\n" +
                "\n" +
                "local cur_mon =t1*12+t2\n" +
                "\n" +
                "local h1=tonumber(string.sub(m1,0,4))\n" +
                "\n" +
                "local h2=tonumber(string.sub(m1,5,6))\n" +
                "\n" +
                "local his_mon = h1*12+h2\n" +
                "\n" +
                "local sub = cur_mon-his_mon\n" +
                "\n" +
                "if sub >= {?HIS_MONTH_COUNT} then\n" +
                "\n" +
                "  db_id = \"{?HIS_BILLING_DB}\"\n" +
                "\n" +
                "else\n" +
                "\n" +
                "  db_id = db_tmp\n" +
                "\n" +
                "end', 'his_billing_db');";
        String rollBackSql = getRollbackSql(sql);
        System.out.println(rollBackSql);
    }
}
